package com.util;

import jxl.CellView;
import jxl.write.WritableSheet;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtils {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);
    public static List<String> excelToShopIdList(InputStream inputStream) {

        List<String> list = new ArrayList<>();
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
            inputStream.close();
            //工作表对象
            Sheet sheet = workbook.getSheetAt(0);
            //总行数
            int rowLength = sheet.getLastRowNum() -1;
//            System.out.println("总行数有多少行"+rowLength);
            //工作表的列
            Row row = sheet.getRow(0);

            //总列数
//            int colLength = row.getLastCellNum();
//            System.out.println("总列数有多少列"+colLength);
            //得到指定的单元格
            Cell cell = row.getCell(0);
            for (int i = 8; i < rowLength; i++) {
                row = sheet.getRow(i);
                //第一列是空的就跳过
                {
//                    System.out.println("行："+row);
                    if(row == null){
                        break;
                    }
//                    cell = row.getCell(0);
//                    String data = cell.toString();
//                    if(StringUtil.isNull(data)) {
//                        break ;
//                    }
                }
                for (int j = 0; j < 7; j++) {
                    cell = row.getCell(j);
                    if (cell != null) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        String data = cell.getStringCellValue();

                        if( StringUtil.isNull(data)  && j==1){
                            break;
                        }
                        data = data.trim();
                        list.add(data);
                        //判断data是否是数字
//                        if (StringUtils.isNumeric(data))
//                            list.add(Integer.parseInt(data));
                    }else {
                        if (j==1){
                            break;
                        }
                        list.add("") ;
                    }
                }
            }

            row = sheet.getRow(0);
            cell = row.getCell(0);
            String shenbaoren = cell.getStringCellValue();
//            System.out.println("申报人："+shenbaoren);
            list.add(shenbaoren);

            row = sheet.getRow(3);
            cell = row.getCell(4);
            String shenbaoBh = cell.getStringCellValue();
//            System.out.println("申报编号："+shenbaoBh);
            list.add(shenbaoBh);

        } catch (Exception e) {
            LOGGER.error("parse excel file error :", e);
        }
        return list ;
    }


//    public static void genExcel(List<Huowu> huowus,String chukuyuanName,String chukuTime, String fileName) throws Exception {
//
////        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
//
//        WritableWorkbook workbook = jxl.Workbook.createWorkbook(new File(fileName));
//        WritableSheet sheet = workbook.createSheet("出库信息", 0);
//
////        List<String> titles = Arrays.asList("issueKey", "标题", "报告人", "创建日期", "状态", "链接");
//        List<Label> labels = new ArrayList<>();
//
//        // 加上标题栏
//        WritableCellFormat title = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 13, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.WHITE));
//        title.setBackground(Colour.SEA_GREEN);
//        title.setAlignment(Alignment.CENTRE);
//        title.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
//        title.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
//
//        labels.add(new Label(0, 0, "出库操作员："+chukuyuanName+"   出库时间："+chukuTime, title));
//
//        WritableCellFormat titleStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
////        title.setBackground(Colour.VERY_LIGHT_YELLOW);
//        titleStyle.setAlignment(Alignment.CENTRE);
//        titleStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
//        titleStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
//
//
//        WritableCellFormat colmStyle = new WritableCellFormat(new WritableFont(WritableFont.createFont("微软雅黑"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK));
////        title.setBackground(Colour.VERY_LIGHT_YELLOW);
//        colmStyle.setAlignment(Alignment.CENTRE);
//        colmStyle.setVerticalAlignment(jxl.format.VerticalAlignment.getAlignment(1));
//        colmStyle.setBorder(Border.NONE, BorderLineStyle.NONE, Colour.BLACK);
//
//        labels.add(new Label(0, 6, "出库货物信息", title));
//
//        labels.add(new Label(0, 7, "货物描述" , titleStyle));
//        labels.add(new Label(1, 7, "货物规格" , titleStyle));
//        labels.add(new Label(2, 7, "参数要求" , titleStyle));
//        labels.add(new Label(3, 7, "出库数量" , titleStyle));
//        labels.add(new Label(4, 7, "单位" , titleStyle));
//        labels.add(new Label(5, 7, "采购原因" , titleStyle));
//        labels.add(new Label(6, 7, "采购员" , titleStyle));
//        labels.add(new Label(7, 7, "库位" , titleStyle));
//
//        for(int i = 0 ; i < huowus.size() ; i++) {
//            Huowu huowu = huowus.get(i) ;
//            labels.add(new Label(0, 8+i, huowu.getMark()+"" , colmStyle));
//            labels.add(new Label(1, 8+i, huowu.getKehu_name() , colmStyle));
//            labels.add(new Label(2, 8+i, huowu.getKehu_status()+"" , colmStyle));
//            labels.add(new Label(3, 8+i, huowu.getZhengjian_name() , colmStyle));
//            labels.add(new Label(4, 8+i, huowu.getPhone()+"" , colmStyle));
//            labels.add(new Label(5, 8+i, huowu.getZhengjian_status()+"" , colmStyle));
//            labels.add(new Label(6, 8+i, huowu.getZhengjian_number() , colmStyle));
//            labels.add(new Label(7, 8+i, huowu.getJingyin_xuke() , colmStyle));
//        }
//
//        for (Label label : labels) {
//            sheet.addCell(label);
//        }
//
//        expandColumns(sheet, 9);
//        sheet.mergeCells(0,0,7,0) ;
//        sheet.mergeCells(0,6,7,6) ;
//        sheet.mergeCells(0,10+huowus.size(),7,10+huowus.size()) ;
////        sheet.mergeCells(0,0,0,1) ;
//        workbook.write();
//        workbook.close();
//
//    }

    private static void expandColumns(WritableSheet sheet, int columnSize) {

        for (int i = 0; i < columnSize; i++) {

            CellView cellView = sheet.getColumnView(i);

            cellView.setSize(5000);

//            cellView.setAutosize(true);

            sheet.setColumnView(i, cellView);
        }


    }

//    public static void main(String[] args) throws Exception{
//        WritableWorkbook workbook = jxl.Workbook.createWorkbook(new File("C:\\Users\\lenovo\\Desktop\\NY-202012035加急.xlsx"));
//        WritableSheet sheet = workbook.createSheet("入库信息", 1);
//
//        sheet.addCell(new Label(0 , 0 , "张三"));
//
//        workbook.write();
//        workbook.close();
//    }


}